﻿CREATE PROCEDURE admin.sec_CreateTriggers AS
CREATE TABLE #tracestatus (PKTABLE_QUALIFIER	sysname NULL	,PKTABLE_OWNER	sysname	NULL,PKTABLE_NAME	sysname	NULL,PKCOLUMN_NAME	sysname	NULL,FKTABLE_QUALIFIER	sysname	NULL,FKTABLE_OWNER	sysname	NULL,FKTABLE_NAME	sysname	NULL,FKCOLUMN_NAME	varchar(32)	NULL,KEY_SEQ	smallint	NULL,UPDATE_RULE	smallint	NULL,DELETE_RULE	smallint	NULL,FK_NAME	sysname	NULL,PK_NAME	sysname NULL,DIFERRABILITY int NULL)

INSERT INTO #tracestatus(FKTABLE_NAME)VALUES('t_Loan')
INSERT INTO #tracestatus(FKTABLE_NAME)VALUES('t_Borrower')
INSERT INTO #tracestatus(FKTABLE_NAME)VALUES('t_Car')
INSERT INTO #tracestatus(FKTABLE_NAME)VALUES('t_CarInfo')
INSERT INTO #tracestatus(FKTABLE_NAME)VALUES('t_Loan_Verification')
INSERT INTO #tracestatus(FKTABLE_NAME)VALUES('t_LoanInfo')
INSERT INTO #tracestatus(FKTABLE_NAME)VALUES('t_Loan_Verification')
INSERT INTO #tracestatus(FKTABLE_NAME)VALUES('t_VerificationSmart')

DECLARE cur CURSOR
READ_ONLY
FOR SELECT DISTINCT FKTABLE_NAME FROM #tracestatus-- WHERE FKTABLE_NAME = 't_References'

DECLARE @TableName sysname,@LoanIDDesc varchar(512)

OPEN cur

FETCH NEXT FROM cur INTO @TableName
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		SET @LoanIDDesc = (SELECT CONVERT(varchar(512),Value)FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @TableName, 'column', 'LoanID'))
		PRINT dbo.sprintf1('sec__InsertTriggers<>@TableName:$0,@LoanIDDesc:$1',@TableName,@LoanIDDesc)
		IF NOT ISNULL(@LoanIDDesc,'') LIKE '%nosec%' BEGIN
			EXEC sp__sec__CreateTrigger @TableName,'INSERT'
			EXEC sp__sec__CreateTrigger @TableName,'UPDATE'
			EXEC sp__sec__CreateTrigger @TableName,'DELETE'
		END
	END
	FETCH NEXT FROM cur INTO @TableName
END
/*
EXEC sp__Sec__CreateTrigger 't_Borrower','INSERT'
EXEC sp__Sec__CreateTrigger 't_Borrower','UPDATE'
EXEC sp__Sec__CreateTrigger 't_Borrower','DELETE'
EXEC sp__Sec__CreateTrigger 't_Loan','INSERT'
EXEC sp__Sec__CreateTrigger 't_Loan','UPDATE','IF Update(MenuID) RETURN'
EXEC sp__Sec__CreateTrigger 't_Loan','DELETE'
*/
CLOSE cur
DEALLOCATE cur
DROP TABLE #tracestatus











